﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.Data;
using Newtonsoft.Json;
using Newtonsoft.Json.Converters;
using Newtonsoft.Json.Linq;
using System.IO;


namespace DBUtility
{
    public class commen
    {

        public static DataTable check_caocan(string content)
        {
            string[] addrecord_arry = content.Split('|');
            string sta;
            try
            {
                DBUtility.Sqlopenhander doh = new Sqlopenhander();
                doh.Reset();
                doh.Sqlcmd = "select * from taocan_list where  Louhao=@deleted and sushe_number=@delet and delect=0 ";
                doh.add_alConditionParameters("@deleted", addrecord_arry[1]);
                doh.add_alConditionParameters("@delet", addrecord_arry[2]);
                DataTable Dt = doh.GetDataTable();
                doh.Dispose();
                return Dt;
            }
            catch (Exception ex)
            {
                return null;
            }
        }


        public static DataTable check_caocan_user(string content)
        {
            string[] addrecord_arry = content.Split('|');
            string sta;
            try
            {
                DBUtility.Sqlopenhander doh = new Sqlopenhander();
                doh.Reset();
                doh.Sqlcmd = "select * from taocan_list where  Louhao=@deleted and sushe_number=@delet and delect=0 ";
                doh.add_alConditionParameters("@deleted", addrecord_arry[1]);
                doh.add_alConditionParameters("@delet", addrecord_arry[2]);
                DataTable Dt = doh.GetDataTable();
                doh.Dispose();
                return Dt;
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        /// <summary>
        /// 获取相应楼号和宿舍的套餐信息，在套餐表拿数据
        /// </summary>
        /// <param name="content"></param>
        /// <returns></returns>
        public static DataTable check_caocan_user_show(string content)
        {
            string[] addrecord_arry = content.Split('|');
            string sta;
            try
            {
                DBUtility.Sqlopenhander doh = new Sqlopenhander();
                doh.Reset();
                doh.Sqlcmd = "select * from taocan_list where  Louhao=@deleted and sushe_number=@delet and delect=0 ";
                doh.add_alConditionParameters("@deleted", addrecord_arry[2]);
                doh.add_alConditionParameters("@delet", addrecord_arry[3]);
                DataTable Dt = doh.GetDataTable();
                doh.Dispose();
                return Dt;
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        
        public static string addrecord(string addrecord)
        {


            DataTable caolist = check_caocan_user(addrecord);
            string shengyu = caolist.Rows[0][6].ToString();
            string qiantong = caolist.Rows[0][7].ToString();
            string[] addrecord_arry = addrecord.Split('|');
            string sta;
            try
            {
                DBUtility.Sqlopenhander doh = new DBUtility.Sqlopenhander();
                doh.Reset();
                doh.add_alFieldItems("id", Guid.NewGuid().ToString());
                doh.add_alFieldItems("songshui_tongshu", addrecord_arry[0]);
                doh.add_alFieldItems("Louhao",int.Parse(addrecord_arry[1]));
                doh.add_alFieldItems("sushe_number", int.Parse(addrecord_arry[2]));
                doh.add_alFieldItems("brand", caolist.Rows[0][4].ToString());
                doh.add_alFieldItems("shengyu_tongshu", shengyu);
                doh.add_alFieldItems("qian_songshui", qiantong);
                doh.add_alFieldItems("area", caolist.Rows[0][1].ToString());
                doh.add_alFieldItems("ask_watertime", DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"));
                doh.add_alFieldItems("beizu", addrecord_arry[3]);
                doh.add_alFieldItems("delect_text", "0是未删除1是已删除");
                doh.add_alFieldItems("delect", "0");
                doh.add_alFieldItems("state", "1");
                doh.add_alFieldItems("state_text", "未送");
                doh.Insert("record_askwater");
                doh.Dispose();
                sta = "叫水成功！";
            }
            catch (Exception ex)
            {
                sta = ex.Message;
            }



            return sta;

        }
        /// <summary>
        /// 检测这个宿舍今天是否已经叫了水，一天只能叫一次
        /// </summary>
        /// <param name="building">楼号</param>
        /// <param name="dorm">宿舍号</param>
        /// <returns></returns>
        public static bool check_askwater_times(string building,string dorm) {
            bool result = true ;
            DBUtility.Sqlopenhander doh = new DBUtility.Sqlopenhander();
            doh.Reset();
            string d_time = DateTime.Now.ToString("yyyy/MM/dd");
            doh.Sqlcmd = " select * from record_askwater where  Louhao=@building and delect !=2 and  sushe_number=@sushe_number and ask_watertime like '" + d_time + "%'";
            doh.add_alConditionParameters("@building", building);
            doh.add_alConditionParameters("@sushe_number", dorm);
            DataTable dt=doh.GetDataTable();
            if(dt.Rows.Count>0){
                result = false;
            }
            return result;
        
        }
        /// <summary>
        /// 这个是到叫水记录表获取套餐信息，属于第一次订购水行的水
        /// </summary>
        /// <param name="content"></param>
        /// <returns></returns>
        public static DataTable get_taocanlist_delete(string content)
        { 
         string[] addrecord_arry = content.Split('|');
            string sta;
            try
            {
                DBUtility.Sqlopenhander doh = new Sqlopenhander();
                doh.Reset();
                doh.Sqlcmd = "select * from record_askwater where  Louhao=@deleted and sushe_number=@delet and delect=2 ";
                doh.add_alConditionParameters("@deleted", addrecord_arry[2]);
                doh.add_alConditionParameters("@delet", addrecord_arry[3]);
                DataTable Dt = doh.GetDataTable();
                doh.Dispose();
                return Dt;
            }
            catch (Exception ex)
            {
                return null;
            }
        } 
        public static string adduserask_water(string addrecord)
        {
            string[] addrecord_arry = addrecord.Split('|');
            DataTable caolist;
            string sta;
            string sql = "";
            // tongshu.toString() + "|" + remark + "|" + Louhao + "|" + sushe_number; + "|" + huishoutongshu + "|" + ask_type 
           //1是套餐列表有的2是没有的。这个是叫水类型，
            if (addrecord_arry[5] == "2")
            {
                if (check_askwater_times(addrecord_arry[2], addrecord_arry[3]))
                {
                    caolist = get_taocanlist_delete(addrecord);
                    DBUtility.Sqlopenhander doh = new DBUtility.Sqlopenhander();
                    string guid=Guid.NewGuid().ToString();
                    doh.Reset();
                    doh.add_alFieldItems("id", guid);
                    doh.add_alFieldItems("songshui_tongshu", addrecord_arry[0]);
                    doh.add_alFieldItems("Louhao", int.Parse(addrecord_arry[2]));
                    doh.add_alFieldItems("sushe_number",int.Parse(addrecord_arry[3]));
                    doh.add_alFieldItems("huishou_tongshu", addrecord_arry[4]);
                    doh.add_alFieldItems("brand", caolist.Rows[0]["brand"].ToString().Replace("A套餐", "").Replace("C套餐", "").Replace("B套餐", ""));
                    doh.add_alFieldItems("area", caolist.Rows[0]["area"].ToString());
                    doh.add_alFieldItems("ask_watertime", DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"));
                    doh.add_alFieldItems("beizu", addrecord_arry[1]);
                    doh.add_alFieldItems("state", "1");
                    doh.add_alFieldItems("state_text", "未送");
                    doh.Insert("record_askwater");
                    doh.Dispose();
                    sql = "方法adduserask_water：id=" + guid + "songshui_tongshu=" + addrecord_arry[0] + "Louhao=" + addrecord_arry[2] + "sushe_number=" + addrecord_arry[3] + "huishou_tongshu=" + addrecord_arry[4] + "brand=" + caolist.Rows[0]["brand"].ToString().Replace("A套餐", "").Replace("C套餐", "").Replace("B套餐", "") +
                             "area=" + caolist.Rows[0]["area"].ToString()
                             + "ask_watertime=" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "beizu=" + addrecord_arry[1];
                    sta = "添加成功";
                }
                else
                {
                    sta = "一个宿舍一天只能叫一次水";
                }
            }
            else {
                 caolist = check_caocan_user_show(addrecord);
                 string shengyu = caolist.Rows[0]["shengyu_tongshu"].ToString();
                 string qiantong = caolist.Rows[0]["qiantong"].ToString();
                 try
                 {
                     if (check_askwater_times(addrecord_arry[2], addrecord_arry[3]))
                     {
                         DBUtility.Sqlopenhander doh = new DBUtility.Sqlopenhander();
                         doh.Reset();
                         string guid = Guid.NewGuid().ToString();
                         doh.add_alFieldItems("id", guid);
                         doh.add_alFieldItems("songshui_tongshu", addrecord_arry[0]);
                         doh.add_alFieldItems("Louhao", addrecord_arry[2]);
                         doh.add_alFieldItems("sushe_number", addrecord_arry[3]);
                         doh.add_alFieldItems("huishou_tongshu", addrecord_arry[4]);
                         doh.add_alFieldItems("brand", caolist.Rows[0]["brand"].ToString());
                         doh.add_alFieldItems("shengyu_tongshu", shengyu);
                         doh.add_alFieldItems("qian_songshui", qiantong);
                         doh.add_alFieldItems("area", caolist.Rows[0]["area"].ToString());
                         doh.add_alFieldItems("ask_watertime", DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"));
                         doh.add_alFieldItems("beizu", addrecord_arry[1]);
                         doh.add_alFieldItems("state", "1");
                         doh.add_alFieldItems("state_text", "未送");
                         doh.Insert("record_askwater");
                         doh.Dispose();
                         sql = "方法adduserask_water：id=" + guid + "songshui_tongshu=" + addrecord_arry[0] + "Louhao=" + addrecord_arry[2] + "sushe_number=" + addrecord_arry[3] + "huishou_tongshu=" + addrecord_arry[4] + "brand=" + caolist.Rows[0]["brand"].ToString().Replace("A套餐", "").Replace("C套餐", "").Replace("B套餐", "") +
                             "shengyu_tongshu=" + shengyu + "qian_songshui=" + qiantong + "area=" + caolist.Rows[0]["area"].ToString()
                             + "ask_watertime=" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "beizu=" + addrecord_arry[1];
                         sta = "添加成功";
                     }
                     else
                     {
                         sta = "一个宿舍一天只能叫一次水";
                     }
                 }
                 catch (Exception ex)
                 {
                     sta = ex.Message;
                 }
            }




            ErrorLog.Log(MapPath_mj("MJLogs/" + DateTime.Now.ToString("yyyyMMdd")), DateTime.Now.ToString() + sql);
          
            return sta;
        
        
        }

        /// <summary>
        /// 定时器是一个支线程，server类不能有在支线程中所以这个方法是获取路径的一个方法
        /// </summary>
        /// <param name="strPath">想要获取的相对路径</param>
        /// <returns></returns>
        public static string MapPath_mj(string strPath)
        {
            if (HttpContext.Current != null)
            {
                return HttpContext.Current.Server.MapPath(strPath);
            }
            else  //非web程序引用           
            {
                strPath = strPath.Replace("/", "\\");
                if (strPath.StartsWith("\\"))
                {                     //strPath = strPath.Substring(strPath.IndexOf('\\', 1)).TrimStart('\\');    
                    strPath = strPath.TrimStart('\\');
                }
                return System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, strPath);
            }
        }
        /// <summary>
        /// 用户添加/提交购买套餐
        /// </summary>
        /// <param name="addormcombo_value"></param>
        /// <returns></returns>
        public static string user_addDormcombo(string addormcombo_value) {
            string[] addormcombo_arry = addormcombo_value.Split('|');
            DataTable dt = gettable_clientlist(addormcombo_arry[4]);
            string state_text = "";
            string state = "";
            string sta;
            string area = dt.Rows[0][2].ToString();
            DataTable dorm_record = gettable_taocanlist(area, addormcombo_arry[0], addormcombo_arry[1]);
            if (dorm_record.Rows.Count > 0)
            {
                state_text = "客户续订";
            }
            else {
                state_text = "新单";
            }
            try
            {
                DBUtility.Sqlopenhander doh = new DBUtility.Sqlopenhander();
                doh.Reset();
                doh.add_alFieldItems("id", Guid.NewGuid());
                doh.add_alFieldItems("area", area);
                doh.add_alFieldItems("Louhao", addormcombo_arry[0]);
                doh.add_alFieldItems("sushe_number", addormcombo_arry[1]);
                doh.add_alFieldItems("brand", addormcombo_arry[2]);
                doh.add_alFieldItems("songshui_tongshu", 0);
                doh.add_alFieldItems("ask_watertime", DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"));
                doh.add_alFieldItems("delect", "2");
                doh.add_alFieldItems("state", "1");
                doh.add_alFieldItems("state_text", "未送");
                doh.add_alFieldItems("beizu", state_text + addormcombo_arry[2]);
                doh.Insert("record_askwater");
                doh.Dispose();
                sta = "订购套餐成功，稍后我们工作人员会上门收钱！";
            }
            catch (Exception ex)
            {
                sta = ex.Message;
            }



            return sta;
        }

  public static string bindout(string id){
      DBUtility.Sqlopenhander doh = new Sqlopenhander();
      doh.Reset();
      doh.ConditionExpress = "id=@name";
      doh.add_alConditionParameters("@name", id);
      doh.add_alFieldItems("state",1);
      int dd=doh.Update("clientlist");
      doh.Dispose();
      return dd == 1 ? "解绑成功，刷新后即不显示！" : "解绑失败！";
    
       }
        public static string addDormcombo(string addormcombo_value)
        {
            
            string[] addormcombo_arry = addormcombo_value.Split('|');
            string state_text="";
            string state="";
            if(addormcombo_arry[9]=="已全支付"){
            state="0";
            }
            else{
             state="1";
            }
            string sta;
            try
            {
                DBUtility.Sqlopenhander doh = new DBUtility.Sqlopenhander();
                doh.Reset();
                doh.add_alFieldItems("area", addormcombo_arry[0]);
                doh.add_alFieldItems("Louhao", addormcombo_arry[1]);
                doh.add_alFieldItems("sushe_number", addormcombo_arry[2]);
                doh.add_alFieldItems("brand", addormcombo_arry[3]);
                doh.add_alFieldItems("tongshu", addormcombo_arry[4]);
                doh.add_alFieldItems("shengyu_tongshu", addormcombo_arry[4]);
                doh.add_alFieldItems("qiantong", "0");
                doh.add_alFieldItems("shuifei", addormcombo_arry[5]);
                doh.add_alFieldItems("yajin", addormcombo_arry[6]);
                doh.add_alFieldItems("yishou", addormcombo_arry[7]);
                doh.add_alFieldItems("shouji", addormcombo_arry[8]);
                doh.add_alFieldItems("state", state);
                doh.add_alFieldItems("state_text", addormcombo_arry[9]);
                doh.add_alFieldItems("time", DateTime.Now);
                doh.add_alFieldItems("delect", 0);
                doh.add_alFieldItems("beizu", addormcombo_arry[10]);
                doh.Insert("taocan_list");
                doh.Dispose();
                sta = "添加成功";
            }
            catch (Exception ex)
            {
                sta = ex.Message;
            }



            return sta;
        
        }
        /// <summary>
        /// 这个是后台员工在套餐列表进行修改的方法
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static string dormcombolist_xiugai(string value)
        {

            string str = "";
            string[] values = value.Split('|');
            string state = "";
            if (values[8] == "已收齐") {
                state = "0";
            }
            else if (values[8] == "未收齐") {
                state = "1";
            }
            try
            {
                string sqlw = "select * from dbo.record_askwater where Louhao in( select Louhao from dbo.taocan_list where id=" + values[0] + ") and" +
                "  sushe_number in ( select sushe_number from dbo.taocan_list where id=" + values[0] + ") and state !='0' and delect='0'";
                DataTable dt = DBHelper.QueryDt(sqlw);
                if (dt.Rows.Count == 0)
                {
                    DBUtility.Sqlopenhander doh = new Sqlopenhander();
                    doh.Reset();
                    doh.ConditionExpress = "id=@name";
                    doh.add_alConditionParameters("@name", values[0]);
                    doh.add_alFieldItems("brand", values[1]);
                    doh.add_alFieldItems("tongshu", values[2]);
                    doh.add_alFieldItems("shengyu_tongshu", values[3]);
                    doh.add_alFieldItems("qiantong", values[4]);
                    doh.add_alFieldItems("shuifei", values[5]);
                    doh.add_alFieldItems("yajin", values[6]);
                    doh.add_alFieldItems("yishou", values[7]);
                    doh.add_alFieldItems("state", state);
                    doh.add_alFieldItems("state_text", values[8]);
                    doh.add_alFieldItems("beizu", values[9]);
                    doh.Update("taocan_list");
                    doh.Dispose();

                    string sql = "方法dormcombolist_xiugai：" + "id=" + values[0] + "&brand=" + values[1] + "&tongshu=" + values[2] + "&shengyu_tongshu=" + values[3] + "&qiantong=" + values[4] + "&shuifei=" + values[5] + "&yajin=" + values[6] + "yishou=" + values[7] + "beizu=" + values[9];
                    ErrorLog.Log(System.Web.HttpContext.Current.Server.MapPath("MJLogs/" + DateTime.Now.ToString("yyyyMMdd")), DateTime.Now.ToString() + sql);
                    str = "修改成功!";
                }
                else {
                    str = "修改失败，原因是当前宿舍的叫水要全部已送才能修改!";
                
                }
            }
            catch (Exception ex)
            {
                str = ex.Message;
                ErrorLog.Log(MapPath_mj("MJLogs/" + DateTime.Now.ToString("yyyyMMdd")), DateTime.Now.ToString() + str);

            }

            return str;
        }

        public static string dormcombolist_delect(string id) {
        string str="";
        try
        {
            DBUtility.Sqlopenhander doh = new Sqlopenhander();
            doh.Reset();
            doh.ConditionExpress = "id=@name";
            doh.add_alFieldItems("delect", 1);
            doh.add_alConditionParameters("@name", id);
            doh.Update("taocan_list");
            doh.Dispose();
            str = "ok";
        }
        catch (Exception ex) {
            str = ex.Message;
        }
        return str;
        }


        public static string index_delect(string id)
        {
            string str = "";
            try
            {
                DBUtility.Sqlopenhander doh = new Sqlopenhander();
                doh.Reset();
                doh.ConditionExpress = "id=@name";
                doh.add_alFieldItems("delect", 1);
                doh.add_alConditionParameters("@name", id);
                doh.Update("record_askwater");
                doh.Dispose();
                str = "ok";
            }
            catch (Exception ex)
            {
                str = ex.Message;
            }
            return str;
        }

        public static DataTable getbinddata(string openid) {
            DBUtility.Sqlopenhander doh = new Sqlopenhander();
            doh.Reset();
            doh.Sqlcmd = "select * from clientlist where  openid=@deleted and state=0 ";
            doh.add_alConditionParameters("@deleted", openid);
            DataTable Dt = doh.GetDataTable();
            doh.Dispose();
            return Dt;
        }
        public static DataTable dromlist_loaddowm(string code)
        {

            string[] arrey = code.Split('|');
            string where = " 1=1 and delect='0'";
            where += arrey[0] == "" ? "" : " and area LIKE " + "'" + arrey[0] + "%'";
            where += arrey[1] == "" ? "" : " and Louhao=" + arrey[1];
            where += arrey[2] == "" ? "" : " and sushe_number=" + arrey[2];
            where += arrey[3] == "全部" ? "" : " and state_text=" + "'" + arrey[3] + "'";
            where += arrey[4] == "" || arrey[4] == "全部" ? "" : " and brand=" + "'" + arrey[4]+ "'";
            DBUtility.Sqlopenhander doh = new Sqlopenhander();
            doh.Reset();
            doh.Sqlcmd = "select * from taocan_list where  " + where + " ";
            DataTable Dt = doh.GetDataTable();
            doh.Dispose();
            return Dt;
        }
        public static string btweent_area(string areaname,string limit) {

          string[] areey = new string[] { "旧白宫", "四合院", "北区女生宿舍", "新白宫", "西区宿舍", "玫瑰园1", "玫瑰园2", "其他" };
          string result = ""; 
            switch(areaname){
             case"旧白宫":
                    result = " Louhao between 7 and 13 ";

            break;
             case "四合院":
            result = " Louhao between 14 and 22 ";

            break;
             case "北区女生宿舍":
            result = " Louhao between 23 and 33 ";

            break;
             case "新白宫":
            result =  " Louhao between 34 and 37 ";

            break;
             case "西区宿舍":
            result = " Louhao between 38 and 43 ";

            break;
             case "玫瑰园1":
            result = " Louhao between 44 and 46 ";

            break;
             case "玫瑰园2":
            result = " Louhao between 47 and 48 ";

            break;
             case "其他":
            result = "Louhao like '%其他%'";

            break;

              }
            return result;
        }

        public static bool check_sushenumber_area(string lowhao,string area){
            bool result = true;
            DBUtility.Sqlopenhander doh = new Sqlopenhander();
            doh.Reset();
            doh.Sqlcmd = "select * from area where  area_name='" + area + "' and Louhao='"+lowhao+"' ";
            DataTable Dt = doh.GetDataTable();
            doh.Dispose();
            if (Dt.Rows.Count > 0) {
                result = false;
            }
            return result;
        
        }
        public static DataTable dromlist_ALL_loaddowm(string code, string area)
        {

            string[] arrey = code.Split('|');
            string areabetween;
            if (arrey[1] == "")
            {
                 areabetween = btweent_area(area,"");
            }
            else {
                if (check_sushenumber_area(arrey[1], area))
                {
                areabetween =" area like '%"+area+"%' ";}
                else{
                    areabetween = " 2=2 ";
                }
                
            }

            string where = " 1=1 and delect='0'";
            where += " and " + areabetween;
            where += arrey[1] == "" ? "" : " and Louhao=" + arrey[1];
            where += arrey[2] == "" ? "" : " and sushe_number=" + arrey[2];
            where += arrey[3] == "全部" ? "" : " and state_text=" + "'" + arrey[3] + "'";
            where += arrey[4] == "" || arrey[4] == "全部" ? "" : " and brand=" + "'" + arrey[4] + "'";
            DBUtility.Sqlopenhander doh = new Sqlopenhander();
            doh.Reset();
            doh.Sqlcmd = "select * from taocan_list where  " + where + " ";
            DataTable Dt = doh.GetDataTable();
            doh.Dispose();
            return Dt;
        }
     
        public static DataTable ask_water_loaddowm(string code) {

            string[] arrey = code.Split('|');
            string where = " 1=1 ";
            where += arrey[0] == "" ? "" : " and area=" + "'" + arrey[0] + "%'";
            where += arrey[1] == "" ? "" : " and Louhao=" + arrey[1];
            where += arrey[2] == "" ? "" : " and sushe_number=" + arrey[2];
            where += arrey[3] == "" ? "" : " and songshui_gong=" + "'" + arrey[3] + "'";
            where += arrey[4] == "" || arrey[4] == "全部" ? "" : " and state_text=" + "'" + arrey[4] + "'";
            where += " and delect='0' ";
            where += " order by Louhao ASC, sushe_number ASC";
           
            DBUtility.Sqlopenhander doh = new Sqlopenhander();
            doh.Reset();
            doh.Sqlcmd = "select * from record_askwater where  " + where + " ";
            DataTable Dt = doh.GetDataTable();
            doh.Dispose();
            return Dt;
        
        }
        public static string area_change_louhao(string area){
            string result = "";
            if (area == "旧白宫") {
                result = " Louhao between 7 and 13 ";
            }
            else if (area == "四合院") {

                result = " Louhao between 14 and 22 ";
            }
            else if (area == "北区女生宿舍")
            {
                result = " Louhao between 23 and 33 ";
            }
            else if (area == "新白宫")
            {
                result = " Louhao between 34 and 37 ";
            }
            else if (area == "西区宿舍")
            {
                result = " Louhao between 38 and 43 ";
            }
            else if (area == "玫瑰园1")
            {
                result = " Louhao between 44 and 46 ";
            }
            else if (area == "玫瑰园2")
            {
                result = " Louhao between 47 and 48 ";
            }
            else {
                result = " 1=1 ";
            }
            return result;
        }


        public static DataTable ask_water_allloaddowm(string code,string area) {

            string[] arrey = code.Split('|');
            string where = " 1=1 ";
            if (arrey[1]!=""){
                where += arrey[1] == "" ? "" : " and Louhao=" + arrey[1];
            }
            else {
                where += " and " + area_change_louhao(area);
            }
            where += arrey[2] == "" ? "" : " and sushe_number=" + arrey[2];
            where += arrey[3] == "" ? "" : " and songshui_gong=" + "'" + arrey[3] + "'";
            where += arrey[4] == "" || arrey[4] == "全部" ? "" : " and state_text=" + "'" + arrey[4] + "'";
            where += " and delect='0' ";
            where += " order by Louhao ASC, sushe_number ASC";
            DBUtility.Sqlopenhander doh = new Sqlopenhander();
            doh.Reset();
            doh.Sqlcmd = "select * from record_askwater where  " + where + " ";
            DataTable Dt = doh.GetDataTable();
            doh.Dispose();
            return Dt;
        
        }
     
        public static DataTable indexdata(int page_current, string where, ref int record_count)
        {

            string where1 = " 1=1 and delect in (0,2) ";
            string[] str_arrey = where.Split('|');
            where1 += str_arrey[0] == "" || str_arrey[0] == "全部" ? "" : " and " + area_change_louhao(str_arrey[0]);
          //  if (str_arrey[0] == "" || str_arrey[0] == "全部") {
                where1 += str_arrey[1] == "" ? "" : " and Louhao=" + str_arrey[1];
          //  }
           
            where1 += str_arrey[2] == "" ? "" : " and sushe_number= '" + str_arrey[2] + "'";
            where1 += str_arrey[3] == "" && str_arrey[4] == "" ? "" : "and createtime BETWEEN  '" + str_arrey[3].Replace("/", "-").Replace("/", "-") + " 00:00:00" + "' and '" + str_arrey[4].Replace("/", "-").Replace("/", "-") + " 23:59:59" + "'";
            where1 += str_arrey[5] == "" || str_arrey[5] == null ? "" : " and songshui_gong='" + str_arrey[5] +"'";
            where1 += str_arrey[6] == "" || str_arrey[6] == "全部" ? "" : " and state_text= '" + str_arrey[6] + "' ";
            string table_name = "record_askwater";
            string fieldkey = "";
            string field_show = "id";
            string field_order = " ask_watertime DESC ";
            int page_size = 10;
            DataTable database = DBUtility.Sqlopenhander.QueryPagedDt(table_name, fieldkey, page_current, page_size, field_show, field_order, where1, ref record_count);
            return database;
        }
        public static void updata_caocan(string louhao, string sushe,int qiantongshu)
        {
            Sqlopenhander doh = new Sqlopenhander();
            doh.Reset();
            doh.ConditionExpress = " Louhao =@name  and  sushe_number =@namee ";
            doh.add_alConditionParameters("@name", louhao);
            doh.add_alConditionParameters("@namee", sushe);
            doh.add_alFieldItems("qiantong", qiantongshu.ToString());
            doh.Update("taocan_list");
            doh.Dispose();
        }
        /// <summary>
        /// 已送水然后更新套餐表
        /// </summary>
        /// <param name="louhao"></param>
        /// <param name="sushe"></param>
        /// <param name="xin_shengyu_border"></param>
        /// <param name="xin_qiantongshu"></param>
        public static void updata_caocan(string louhao, string sushe, int xin_shengyu_border, int xin_qiantongshu)
        {
            Sqlopenhander doh = new Sqlopenhander();
            doh.Reset();
            doh.ConditionExpress = " Louhao =@name  and  sushe_number =@namee ";
            doh.add_alConditionParameters("@name", louhao);
            doh.add_alConditionParameters("@namee", sushe);
            doh.add_alFieldItems("shengyu_tongshu", xin_shengyu_border.ToString());
            doh.add_alFieldItems("qiantong", xin_qiantongshu.ToString());
            doh.Update("taocan_list");
            doh.Dispose();
            string sql = "方法updata_caocan：Louhao =" + louhao + "&sushe_number =" + sushe + "&shengyu_tongshu=" + xin_shengyu_border.ToString() + "&qiantong=" + xin_qiantongshu.ToString();
            ErrorLog.Log(MapPath_mj("MJLogs/" + DateTime.Now.ToString("yyyyMMdd")), DateTime.Now.ToString() + sql);
       
        }
        public static string updata_askwater_huishoutong(string id, string huihou)
        {
            DataTable dt = get_askwater_qiantong(id);
            string result = "";
            if (dt.Rows.Count!=0)
            {
            string louhao = dt.Rows[0][2].ToString();
            string sushe = dt.Rows[0][3].ToString();
            string songshui_tongshu = dt.Rows[0][6].ToString();
            string qian_tongshu = dt.Rows[0][8].ToString();
            int xin_qiantongshu = int.Parse(songshui_tongshu) + int.Parse(qian_tongshu) - int.Parse(huihou);
            updata_caocan(louhao,sushe,xin_qiantongshu);
            Sqlopenhander doh = new Sqlopenhander();
            doh.Reset();
            doh.ConditionExpress = " id =@name    ";
            doh.add_alConditionParameters("@name", id);
            doh.add_alFieldItems("qian_songshui", xin_qiantongshu);
            doh.add_alFieldItems("qian_songshui_state", "1");
            doh.add_alFieldItems("qian_songshui_statext", "已修改");
            doh.Update("record_askwater");
            doh.Dispose();
            result = "修改成功";
            }
            else{
                result = "回收桶数不能再次修改！";
            }
            return result;
        }
        /// <summary>
        /// 根据楼号和宿舍号获取相应的宿舍套餐信息
        /// </summary>
        /// <param name="louhao">楼号</param>
        /// <param name="sushe_number">宿舍号</param>
        /// <returns></returns>
        public static DataTable caocan(string louhao,string sushe_number) {
            //Louhao =@name  and  sushe_number =@namee  delect的0是未删除，1已删除
            DBUtility.Sqlopenhander doh = new Sqlopenhander();
            doh.Reset();
            doh.Sqlcmd = "select * from taocan_list where Louhao =@lou  and  sushe_number =@sumber and delect='0'";
            doh.add_alConditionParameters("@lou", louhao);
            doh.add_alConditionParameters("@sumber", sushe_number);
            DataTable Dt = doh.GetDataTable();
            doh.Dispose();
            return Dt;
        
        }



        public static string updata_askwater_yisong(string content)
        {
            string[] arrey = content.Split('|');
            string result = "";
            int statee = 0;
            string state_text = "已送";
            DataTable dt = get_askwater_yisong(arrey[0]);
           
            if(dt.Rows.Count!=0){
            string louhao = dt.Rows[0][2].ToString();
            string sushe = dt.Rows[0][3].ToString();
            DataTable caocan_dt = caocan(louhao, sushe);
            string old_shengyu_border = caocan_dt.Rows[0]["shengyu_tongshu"].ToString();
                //回收桶数
            string old_qian_tongshu = dt.Rows[0]["qian_songshui"].ToString();
            string old_song_tongshu = dt.Rows[0]["songshui_tongshu"].ToString();
            int xin_qiantongshu = int.Parse(old_song_tongshu) + int.Parse(old_qian_tongshu) - int.Parse(arrey[1]);
                //已送
            // old_shengyu_border = dt.Rows[0]["shengyu_tongshu"].ToString();
            int xin_shengyu_border = int.Parse(old_shengyu_border) - int.Parse(old_song_tongshu);

            updata_caocan(louhao, sushe, xin_shengyu_border, xin_qiantongshu);
            Sqlopenhander doh = new Sqlopenhander();
            doh.Reset();
            doh.ConditionExpress = " id =@name    ";
            doh.add_alConditionParameters("@name", arrey[0]);
            doh.add_alFieldItems("qian_songshui", xin_qiantongshu);
            doh.add_alFieldItems("shengyu_tongshu", xin_shengyu_border);
            doh.add_alFieldItems("huishou_tongshu", arrey[1]);
            doh.add_alFieldItems("state", statee);
            doh.add_alFieldItems("state_text", state_text);
            doh.Update("record_askwater");
            doh.Dispose();
            result = "修改成功";
            
            }
            else{
                result = "已送状态和回收桶数只能提交一次！";
            }
            return result;
        }





        public static DataTable get_askwater_qiantong(string id) {

            DBUtility.Sqlopenhander doh = new Sqlopenhander();
            doh.Reset();
            doh.Sqlcmd = "select * from record_askwater where  id=@deleted and delect=0 and state=0 ";
            doh.add_alConditionParameters("@deleted", id);
            DataTable Dt = doh.GetDataTable();
            doh.Dispose();
            return Dt;
        
        }
        /// <summary>
        /// 根据id获取未送的叫水记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static DataTable get_askwater_yisong(string id)
        {

            DBUtility.Sqlopenhander doh = new Sqlopenhander();
            doh.Reset();
            doh.Sqlcmd = "select * from record_askwater where  id=@deleted and delect=0 and state!=0 ";
            doh.add_alConditionParameters("@deleted", id);
            DataTable Dt = doh.GetDataTable();
            doh.Dispose();
            return Dt;

        }
        public static string xiugai_content(string content) {
            //"+id +"|"+ huishou_tongshu+"|"+state 
            string[]  arrey=content.Split('|');
            string state="";
            int statee = 0;
            string state_text = "";
            DataTable dt = get_askwater_yisong(arrey[0]);
            if (dt.Rows.Count != 0)
            {
                if (arrey[2] == "已送")
                {
                    return state = updata_askwater_yisong(content);
                }
                else if (arrey[2] == "未送")
                {
                    statee = 1;
                    state_text = "未送";
                    Sqlopenhander doh = new Sqlopenhander();
                    doh.Reset();
                    doh.ConditionExpress = " id =@name    ";
                    doh.add_alConditionParameters("@name", arrey[0]);
                    doh.add_alFieldItems("state", statee);
                    doh.add_alFieldItems("state_text", state_text);
                    int shu = doh.Update("record_askwater");
                    state = shu == 1 ? "修改成功" : "修改失败";
                    doh.Dispose();
                    return state;
                }
                else if (arrey[2] == "在送")
                {
                    statee = 2;
                    state_text = "在送";
                    Sqlopenhander doh = new Sqlopenhander();
                    doh.Reset();
                    doh.ConditionExpress = " id =@name    ";
                    doh.add_alConditionParameters("@name", arrey[0]);
                    doh.add_alFieldItems("state", statee);
                    doh.add_alFieldItems("state_text", state_text);
                    int shu = doh.Update("record_askwater");
                    state = shu == 1 ? "修改成功" : "修改失败";
                    doh.Dispose();
                    return state;
                }
            }
            else {
                string sql = "update dbo.record_askwater set  huishou_tongshu='" + arrey[1] + "' where id='" + arrey[0] + "'";
                DBHelper.ExcuteSql(sql);
                state = "已送状态再次修改，并不会同步到客户套餐列表，相应的数据还要到套餐列表修改！";
            }
           
          
            return state;
        }
        

        public static string bindusername(string data) {
            string[] date = data.Split('|');
         string sta;
            try
            {
                if (jugement(date[4]))
                {
               
                DBUtility.Sqlopenhander doh = new DBUtility.Sqlopenhander();
                doh.Reset();
                doh.add_alFieldItems("openid", date[4]);
                doh.add_alFieldItems("area", date[0]);
                doh.add_alFieldItems("building", date[1]);
                doh.add_alFieldItems("drom", date[2]);
                doh.add_alFieldItems("number", date[3]);
                doh.add_alFieldItems("time", DateTime.Now);
                doh.Insert("clientlist");
                doh.Dispose();
                sta = "绑定成功";
                }
                else{
                sta="已经绑定无需重复";}
            }
            catch(Exception ex){
                 sta = ex.Message;
            }
            return sta;
        }

        public static bool jugement(string openid) {
                bool state ;
                DBUtility.Sqlopenhander doh = new Sqlopenhander();
                doh.Reset();
                doh.Sqlcmd = "select * from clientlist where  openid=@deleted and state=0";
                doh.add_alConditionParameters("@deleted", openid);
                DataTable Dt = doh.GetDataTable();
                doh.Dispose();
                return state=Dt.Rows.Count==0?true:false;
        }


        public static string getuser_record(string openid) {
            string checkresult;
            try
            {
                DataTable taocan_list = gettable_clientlist(openid);
                if(taocan_list.Rows.Count!=0){
                    string area=taocan_list.Rows[0][2].ToString();
                    string building = taocan_list.Rows[0][3].ToString();
                    string drom = taocan_list.Rows[0][4].ToString();
                    checkresult ="ok_have"+ area+"|"+building+"|"+drom;
                }
                else{
                     checkresult = "你的手机没有绑定,请到绑定页面先绑定！";
                }
               
            }
            catch (Exception ex)
            {
                checkresult= ex.Message;
            }
            return checkresult;
        }



        public static DataTable gettable_taocanlist(string area, string building, string drom)
        {
            DBUtility.Sqlopenhander doh = new Sqlopenhander();
            doh.Reset();
            doh.Sqlcmd = "select * from taocan_list where Louhao=@Louhao and sushe_number=@sushe_number and delect=0";
           
            doh.add_alConditionParameters("@Louhao", building);
            doh.add_alConditionParameters("@sushe_number", drom);
            DataTable Dt = doh.GetDataTable();
            doh.Dispose();
            return Dt;
        }
        public static DataTable gettable_clientlist(string openid)
        {
            DBUtility.Sqlopenhander doh = new Sqlopenhander();
            doh.Reset();
            doh.Sqlcmd = "select * from clientlist where  openid=@deleted and state=0";
            doh.add_alConditionParameters("@deleted", openid);
            DataTable Dt = doh.GetDataTable();
            doh.Dispose();
            return Dt;
        }
        public static DataTable gettable_record_askwater(string area,string building,string drom)
        {
            DBUtility.Sqlopenhander doh = new Sqlopenhander();
            doh.Reset();
            doh.Sqlcmd = "select " + area + " * from record_askwater where   Louhao=@Louhao and sushe_number=@sushe_number order by ask_watertime desc  ";
            
            doh.add_alConditionParameters("@Louhao", building);
            doh.add_alConditionParameters("@sushe_number", drom);
            DataTable Dt = doh.GetDataTable();
            doh.Dispose();
            return Dt;
        }


        public static DataTable clientlist(int page_current, string where, ref int record_count)
        {


            string table_name = "clientlist";
            string fieldkey = "";
            string field_show = "id";
            string field_order = " time desc  ";
            int page_size = 10;
            DataTable database = DBUtility.Sqlopenhander.QueryPagedDt(table_name, fieldkey, page_current, page_size, field_show, field_order, where, ref record_count);
            return database;
        }


        public static DataTable dormcombolist(int page_current, string where, ref int record_count)
        {
            string where1 =  " 1=1 and delect ='0' ";
            string[] str_arrey = where.Split('|');
            string areabetween = btweent_area(str_arrey[0], "");

            where1 += str_arrey[0] == "" || str_arrey[0] == "全部" ? "" : " and " + areabetween;
            where1 += str_arrey[1] == "" ? "" : " and Louhao=" + str_arrey[1];
            where1 += str_arrey[2] == "" ? "" : " and sushe_number=" + str_arrey[2];
            where1 += str_arrey[3] == "" || str_arrey[3] == "全部" ? "" : "" + str_arrey[3] == "已收齐" ? " and state=0 " : " and state=1 ";
            where1 += str_arrey[4] == "" || str_arrey[4] == "全部" ? "" : " and brand= '" + str_arrey[4] + "' ";
            string table_name = "taocan_list";
            string fieldkey = "";
            string field_show = "id";
            string field_order = " Louhao ASC,sushe_number ASC ";
            int page_size = 10;
            DataTable database = DBUtility.Sqlopenhander.QueryPagedDt(table_name, fieldkey, page_current, page_size, field_show, field_order, where1, ref record_count);
            return database;
        }
       
        
        
        public static string login(string username,string pwd) {
            string result = "error";
            Sqlopenhander doh = new Sqlopenhander();
            doh.Reset();
            doh.Sqlcmd = " select * from manager where username=@user and password =@pwd and user_type in ('1','2') ";
            doh.add_alConditionParameters("@user",username);
            doh.add_alConditionParameters("@pwd",pwd);
            DataTable datable_result= doh.GetDataTable();
            doh.Dispose();
            if (datable_result.Rows.Count != 0)
            {

                result = "OK";
            }
            else { }
            return result;
        }

        public static DataTable get_manager(int page_current,string where,ref int allcount) {
            string table_name = "manager";
            string fieldkey = "";
            string field_show = " id ";
            string field_order = "";
            int page_size = 10;
            return DBUtility.Sqlopenhander.QueryPagedDt(table_name, fieldkey, page_current, page_size, field_show, field_order, where, ref allcount);
          
        }

        public static string outbint(string openid) {

            Sqlopenhander doh = new Sqlopenhander();
            doh.Reset();
            doh.ConditionExpress = " openid =@name  and  state=0 ";
            doh.add_alConditionParameters("@name", openid);
            doh.add_alFieldItems("state", 1);
            int state = doh.Update("clientlist");
            doh.Dispose();
            return state != 0 ? "解绑成功" : "解绑失败";
        }

        public static void delete_manager(string id) {
            Sqlopenhander doh = new Sqlopenhander();
            doh.Reset();
            doh.ConditionExpress = " id =@name    ";
            doh.add_alConditionParameters("@name", id);
            doh.add_alFieldItems("state", 1);
            doh.Update("manager");
            doh.Dispose();
        }


        public static string changge_pwd(string username,string pwd) {

            Sqlopenhander doh = new Sqlopenhander();
            doh.Reset();
            string state;
            doh.ConditionExpress = " username =@name    ";
            doh.add_alConditionParameters("@name", username);
            doh.add_alFieldItems("password", pwd);
           int shu= doh.Update("manager");
           if (shu == 1)
           {
               state = "修改成功";
           }
           else { state = "修改失败，可能原因没有这个账户！"; }
            doh.Dispose();
            return state;
        }
        public static string addmanager(string username, string pwd)
        {
            string sta;
            try
            {
                DBUtility.Sqlopenhander doh = new DBUtility.Sqlopenhander();
                doh.Reset();
                doh.add_alFieldItems("username", username);
                doh.add_alFieldItems("password", pwd);
                doh.Insert("manager");
                doh.Dispose();
                sta = "添加成功";
            }
            catch(Exception ex){
                 sta = ex.Message;
            }
            return sta;
        }

        public static void save_castomer(string name, string phone, string parent_id,string activity_name,int state)
        {

            string id = Guid.NewGuid().ToString();
            //string createtime = DateTime.Now.ToString("yyyyMMddHHmmssfff");
            DBUtility.Sqlopenhander doh = new DBUtility.Sqlopenhander();
            doh.Reset();
            doh.add_alFieldItems("customer_id", id);
            doh.add_alFieldItems("customer_name", name);
            doh.add_alFieldItems("activities_ip", parent_id);
            doh.add_alFieldItems("number", phone);
            doh.add_alFieldItems("activity_name", activity_name);
            doh.add_alFieldItems("signup_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            doh.add_alFieldItems("State", state);
            doh.Insert("Customer");
            doh.Dispose();
        }
     
        public static void updata(string biao_ming,string number,string activity_name,string customer_name) {
            DBUtility.Sqlopenhander doh = new Sqlopenhander();
            doh.Reset();
            doh.ConditionExpress = " activity_name =@name  and number=@num  ";
            doh.add_alConditionParameters("@name",activity_name);
            doh.add_alConditionParameters("@num", number);
            doh.add_alFieldItems("customer_name",customer_name);
            doh.add_alFieldItems("State", 2);
            doh.Update(biao_ming);
            doh.Dispose();
        }
        /// <summary>
        /// 获取照片展示
        /// </summary>
        /// <returns></returns>
        public static DataTable get_picture()
        {
            try
            {
                DBUtility.Sqlopenhander doh = new Sqlopenhander();
                doh.Reset();
                doh.Sqlcmd = "select * from img where  delected=@deleted";
                doh.add_alConditionParameters("@deleted", 0);
                DataTable Dt = doh.GetDataTable();
                doh.Dispose();
                return Dt;
            }
            catch (Exception ex)
            {
                return null;
            }
        }
      
        /// <summary>
        /// 删除活动
        /// </summary>
        public static void delect_activity()
        {
            DBUtility.Sqlopenhander doh = new Sqlopenhander();
            doh.Reset();
            doh.ConditionExpress = "delected=@name";
            doh.add_alConditionParameters("@name", 0);
            doh.add_alFieldItems("delected", 1);
            doh.Update("activities");
            doh.Dispose();
        }
        /// <summary>
        /// 获取数据到号码库页面
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public static DataTable get_base(string where)
        {

            DBUtility.Sqlopenhander doh = new Sqlopenhander();
            doh.Reset();
            try
            {
                doh.Sqlcmd = "select customer_name,number from Customer";
                doh.add_alConditionParameters("@limite", where);
                DataTable data = doh.GetDataTable();
                doh.Dispose();
                return data;
            }
            catch (Exception ex)
            {
                return null;
            }

        }
        /// <summary>
        /// 现在这个方法是导入时成功后去数据给他们看还有就是导出时用到的方法
        /// </summary>
        /// <param name="page_current"></param>
        /// <param name="where"></param>
        /// <param name="record_count"></param>
        /// <returns></returns>
        public static DataTable get_numberbase(int page_current, string where, ref int record_count)
        {
           return get_numberbase(page_current, 32, where, ref  record_count);
           
        }

        public static DataTable get_numberbase(int page_current, int page_size, string where, ref int record_count)
        {
            string table_name = "black_white";
            string fieldkey = "";
            string field_show = "";
            string field_order = "";
            DataTable database = DBUtility.Sqlopenhander.QueryPagedDt(table_name, fieldkey, page_current, page_size, field_show, field_order, where, ref record_count);
            return database;
        }

        /// <summary>
        /// 现在这个方法只是页面获取数据的时候用到
        /// </summary>
        /// <param name="page_current"></param>
        /// <param name="where"></param>
        /// <param name="record_count"></param>
        /// <returns></returns>
        public static DataTable get_numberbase1(int page_current, string where, ref int record_count)
        {
            return get_numberbase1(page_current, 32, where, ref  record_count);

        }

        public static DataTable get_numberbase1(int page_current, int page_size, string where, ref int record_count)
        {//black_white
            string table_name = "black_white";
            string fieldkey = "";
            string field_show = "";
            string field_order = "";
            DataTable database = DBUtility.Sqlopenhander.QueryPagedDt(table_name, fieldkey, page_current, page_size, field_show, field_order, where, ref record_count);
            return database;
        }
        public static DataTable get_data(int page_current,string where, ref int record_count)
        {//desc
            return get_data(page_current, 10, where, ref  record_count);
        }
        public static DataTable get_data(int page_current,int page_size, string where, ref int record_count)
        {
            string table_name = "huo_dong_jilu";
            string FieldKey = "";
           
            string field_show = "*";
            string field_order = "";
            DataTable messege = DBUtility.Sqlopenhander.QueryPagedDt(table_name, FieldKey, page_current, page_size, field_show, field_order, where, ref record_count);
            return messege;
        }
        /// <summary>
        ///获取活动列表显示页头
        /// </summary>
        /// <returns></returns>
        public static DataTable get_activity_list()
        {
            DBUtility.Sqlopenhander doh = new Sqlopenhander();
            doh.Reset();
            doh.Sqlcmd = "select activities_name from activities order by create_time desc";
            DataTable lidt = doh.GetDataTable();
            doh.Dispose();
            return lidt;
        }

        public static DataTable get_activity_list(string activities_name)
        { 
              DBUtility.Sqlopenhander doh = new Sqlopenhander();
            doh.Reset();
            doh.Sqlcmd = "select * from activities  where activities_name = '" + activities_name + "'";
            DataTable dt = doh.GetDataTable();
            doh.Dispose();
            return dt;
        }
    }
}
